using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime;

namespace Microsoft.SharedSource.SqlServer.MDDE.Components
{
    [
        System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1705:LongAcronymsShouldBePascalCased"),
        SupportedComponentType("{0FB4AABB-C027-4440-809A-1198049BF117}"),
    ]
    class MDDEDataFlowLookupTransformation : MDDEDataFlowComponent
    {
        #region Member Variables

        private XmlNodeList changedProperties = null;
        private IDTSCustomPropertyCollection100 customProperties = null;
        XmlNodeList collectionInformation = null;
        bool preservePreviousCollections = true;

        //private Package originalPackage = new Package();
        IDTSComponentMetaData100 component = null;

        #endregion

        public MDDEDataFlowLookupTransformation(Package package, Object component, string name, string uri)
            : base(package, component, name, uri)
        {
        }

        #region Overridden Methods

        public override void GetChangeXml(MDDEComponent subject, XmlDocument changeXml)
        {
            #region Set Values

            if (subject == null)
                throw new ArgumentNullException("Subject can not NULL");

            if (!(subject is MDDEDataFlowLookupTransformation))
                throw new ArgumentException("Subject is not of type MDDEDataFlowLookupTransformation");

            component = (IDTSComponentMetaData100)innerComponent;
            IDTSComponentMetaData100 referenceComponent = (IDTSComponentMetaData100)subject.InnerComponent;

            IDTSCustomPropertyCollection100 componentPropertyCollection = (IDTSCustomPropertyCollection100)component.CustomPropertyCollection;
            IDTSCustomPropertyCollection100 referenceComponentPropertyCollection = (IDTSCustomPropertyCollection100)referenceComponent.CustomPropertyCollection;

            XmlNode componentNode = null;
            componentNode = changeXml.CreateElement("Component");

            #endregion

            #region Compare properties of Component with subject and create Properties' Xml

            for (int propertyIndex = 0; propertyIndex < componentPropertyCollection.Count; propertyIndex++)
            {
                if (componentPropertyCollection[propertyIndex].Name.CompareTo("Name") == 0)
                    continue;

                IDTSCustomProperty100 componentProperty = componentPropertyCollection[propertyIndex];
                IDTSCustomProperty100 referenceComponentProperty = referenceComponentPropertyCollection[propertyIndex];

                string componentPropertyValue = String.Empty;
                if (componentProperty.Value != null)
                    componentPropertyValue = componentProperty.Value.ToString();

                string referenceComponentPropertyValue = String.Empty;
                if (referenceComponentProperty.Value != null)
                    referenceComponentPropertyValue = referenceComponentProperty.Value.ToString();

                if (componentPropertyValue != referenceComponentPropertyValue)
                {
                    UpdateNodeProperties(changeXml, componentNode, componentProperty.Name, componentPropertyValue);
                }
            }

            #endregion

            #region Extract Custom Collections from Component

            UpdateNodeCollections(changeXml, componentNode, false);

            #region Get Input Collection Properties that differ from subject component

            foreach (IDTSCustomProperty100 componentProperty in component.InputCollection[0].CustomPropertyCollection)
            {
                foreach (IDTSCustomProperty100 referenceComponentProperty in referenceComponent.InputCollection[0].CustomPropertyCollection)
                {
                    if (referenceComponentProperty.Name == componentProperty.Name)
                    {
                        if (referenceComponentProperty.Value != componentProperty.Value)
                        {
                            string propertyValue = String.Empty;
                            if (componentProperty.Value != null)
                                propertyValue = componentProperty.Value.ToString();

                            UpdateNodeCollectionProperties(changeXml, componentNode, false,
                                component.InputCollection[0].Name, "Input",
                                componentProperty.Name, propertyValue);
                        }

                        break;
                    }
                }
            }

            #endregion

            #region Get Output Collection Properties that differ from subject component

            foreach (IDTSCustomProperty100 componentProperty in component.OutputCollection[0].CustomPropertyCollection)
            {
                foreach (IDTSCustomProperty100 referenceComponentProperty in referenceComponent.OutputCollection[0].CustomPropertyCollection)
                {
                    if (referenceComponentProperty.Name == componentProperty.Name)
                    {
                        if (referenceComponentProperty.Value != componentProperty.Value)
                        {
                            string propertyValue = String.Empty;
                            if (componentProperty.Value != null)
                                propertyValue = componentProperty.Value.ToString();

                            UpdateNodeCollectionProperties(changeXml, componentNode, false,
                                component.OutputCollection[0].Name, "Output",
                                componentProperty.Name, propertyValue);
                        }

                        break;
                    }
                }
            }

            #endregion

            #region Get Input Collection's Column Properties

            foreach (IDTSInputColumn100 inputColumn in component.InputCollection[0].InputColumnCollection)
            {
                foreach (IDTSCustomProperty100 columnProperty in inputColumn.CustomPropertyCollection)
                {
                    string columnPropertyValue = String.Empty;
                    if (columnProperty.Value != null)
                        columnPropertyValue = columnProperty.Value.ToString();

                    UpdateNodeCollectionColumnProperties(changeXml, componentNode, false,
                        component.InputCollection[0].Name, "Input", inputColumn.Name,
                        columnProperty.Name, columnPropertyValue);
                }
            }

            #endregion

            #region Get Output Collection's Column Properties

            foreach (IDTSOutputColumn100 outputColumn in component.OutputCollection[0].OutputColumnCollection)
            {
                foreach (IDTSCustomProperty100 columnProperty in outputColumn.CustomPropertyCollection)
                {
                    string columnPropertyValue = String.Empty;
                    if (columnProperty.Value != null)
                        columnPropertyValue = columnProperty.Value.ToString();

                    UpdateNodeCollectionColumnProperties(changeXml, componentNode, false,
                        component.OutputCollection[0].Name, "Output", outputColumn.Name,
                        columnProperty.Name, columnPropertyValue);
                }
            }

            #endregion

            #endregion

            #region Update Changes Xml

            AddAttribute(changeXml, componentNode, "Name", this.name);
            AddAttribute(changeXml, componentNode, "URL", this.uri);
            AddAttribute(changeXml, componentNode, "RowsetDescriptor", "");

            changeXml.FirstChild.AppendChild(componentNode);

            #endregion
        }

        public override bool ApplyChanges(XmlDocument changeXml, MDDEExecutionDAG originalPackage)
        {
            try
            {
                #region Set Values

                XmlNode componentChangeXml =
                    changeXml.SelectSingleNode("//PackageChanges/Component[@URL='" + this.uri + "']");

                //string packageXml = null;
                //package.SaveToXML(out packageXml, null);
                //originalPackage.LoadFromXML(packageXml, null);

                //if (innerComponent == null)
                //    return false;

                //if (!(innerComponent is IDTSComponentMetaData100))
                //    return false;

                component = (IDTSComponentMetaData100)innerComponent;

                #endregion

                #region Read Change Xml

                if (componentChangeXml != null)
                {
                    if (!ReadChangeXml(componentChangeXml))
                        return false;
                }

                #endregion

                #region Apply Properties and Verify If Sufficient Property Values' Are Available

                if (!ApplyProperties())
                    return false;

                #endregion

                #region Check for Connection Manager in Runtime Collection

                if (component.RuntimeConnectionCollection.Count > 0)
                {
                    string connectionManagerID = component.RuntimeConnectionCollection[0].ConnectionManagerID;

                    if (String.IsNullOrEmpty(connectionManagerID))
                        return false;

                    component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(this.package.Connections[connectionManagerID]);
                }

                #endregion

                #region Clear Invalid Input Columns

                component.RemoveInvalidInputColumns();

                #endregion

                #region Set Reference Metadata Xml

                XmlNodeList referenceColumns;
                IDTSDesigntimeComponent100 designTimeComponent = component.Instantiate();

                if (!SetReferenceMetadataXml(out referenceColumns, designTimeComponent))
                    return false;

                #endregion

                #region Create Collections

                //ObjectType objectType = ObjectType.Package;
                //IDTSComponentMetaData100 originalComponent
                //    = (IDTSComponentMetaData100)this.GetComponent(this.originalPackage, uri, ref objectType);

                IDTSComponentMetaData100 originalComponent =
                        (originalPackage.GetMDDEComponentByUri(uri)).InnerComponent as IDTSComponentMetaData100;

                IDTSInput100 originalInput = originalComponent.InputCollection[0];
                IDTSInput100 input = component.InputCollection[0];

                IDTSOutput100 originalOutput = originalComponent.OutputCollection[0];
                IDTSOutput100 output = component.OutputCollection[0];

                IDTSVirtualInput100 virutalInput = input.GetVirtualInput();

                IDTSVirtualInputColumnCollection100 virtualInputColumnCollection
                    = virutalInput.VirtualInputColumnCollection;

                #region Preserve Previous Collection Settings or Clear Collections based on User Preference

                component.InputCollection[0].InputColumnCollection.RemoveAll();
                component.OutputCollection[0].OutputColumnCollection.RemoveAll();

                if (preservePreviousCollections)
                {
                    RestorePreviousCollections(referenceColumns, designTimeComponent,
                        originalInput, input, originalOutput, output, virutalInput,
                        virtualInputColumnCollection);
                }

                #endregion

                #region Override Collections with New Custom Collections

                if (collectionInformation != null)
                {
                    ApplyCustomCollections(referenceColumns, designTimeComponent,
                        input, output, virutalInput, virtualInputColumnCollection);
                }

                #endregion

                #region Verify validity of Input And Output Columns

                ValidateInputAndOutputColumns(designTimeComponent, input,
                    output, virutalInput, virtualInputColumnCollection);

                #endregion

                #endregion

                #region Reinitialize Component

                designTimeComponent.AcquireConnections(null);

                DTSValidationStatus validationStatus = designTimeComponent.Validate();

                if (validationStatus == DTSValidationStatus.VS_ISBROKEN)
                {
                    designTimeComponent.ReleaseConnections();
                    return false;
                }

                designTimeComponent.ReinitializeMetaData();
                designTimeComponent.ReleaseConnections();

                #endregion

                return true;
            }

            catch
            {
                // TODO: Add to Log
                return false;
            }
        }
        
        public override bool Validate()
        {
            try
            {
                #region Check for Connection Manager in Runtime Collection

                if (((IDTSComponentMetaData100)innerComponent).RuntimeConnectionCollection.Count > 0)
                {
                    string connectionManagerID
                        = ((IDTSComponentMetaData100)innerComponent).RuntimeConnectionCollection[0].ConnectionManagerID;

                    if (String.IsNullOrEmpty(connectionManagerID))
                        return false;

                    ((IDTSComponentMetaData100)innerComponent).RuntimeConnectionCollection[0].ConnectionManager
                        = DtsConvert.ToConnectionManager90(this.package.Connections[connectionManagerID]);
                }

                #endregion

                IDTSDesigntimeComponent100 designTimeComponent = ((IDTSComponentMetaData100)innerComponent).Instantiate();

                designTimeComponent.AcquireConnections(null);
                DTSValidationStatus validationStatus = designTimeComponent.Validate();
                designTimeComponent.ReleaseConnections();

                if (validationStatus == DTSValidationStatus.VS_ISVALID)
                    return true;
                else
                    return false;
            }

            catch
            {
                // TODO: Add to Log
                return false;
            }
        }

        #endregion

        #region Helper Methods

        private bool ReadChangeXml(XmlNode changeXml)
        {
            if (changeXml.SelectSingleNode("Properties") != null)
                changedProperties = changeXml["Properties"].ChildNodes;

            if (component.CustomPropertyCollection == null)
                return false;

            customProperties = (IDTSCustomPropertyCollection100)component.CustomPropertyCollection;

            if (customProperties == null)
                return false;
            
            if (changeXml.SelectSingleNode("CollectionInformation") != null)
            {
                if (changeXml["CollectionInformation"].ChildNodes.Count > 0)
                    collectionInformation = changeXml["CollectionInformation"].ChildNodes;

                if (changeXml["CollectionInformation"].Attributes["PreservePreviousCollections"] != null)
                    preservePreviousCollections = Convert.ToBoolean(
                        changeXml["CollectionInformation"].Attributes["PreservePreviousCollections"].Value);
            }

            return true;
        }

        private bool ApplyProperties()
        {
            if (changedProperties != null)
            {
                foreach (XmlNode changedProperty in changedProperties)
                    customProperties[changedProperty.Attributes["Name"].Value].Value = this.ConvertToCompatibleType(changedProperty.Attributes["Value"].Value,
                        customProperties[changedProperty.Attributes["Name"].Value].Value.GetType().Name);
            }

            if (component.Instantiate().Validate()
                == DTSValidationStatus.VS_ISBROKEN)
                return false;
            else
                return true;
        }

        private Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType ConvertStringToDataType(string dataTypeString)
        {
            switch (dataTypeString)
            {
                case "DT_BOOL":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_BOOL;

                case "DT_BYTES":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_BYTES;

                case "DT_CY":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_CY;

                case "DT_DATE":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_DATE;

                case "DT_DBDATE":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_DBDATE;

                case "DT_DBTIME":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_DBTIME;

                case "DT_DBTIMESTAMP":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_DBTIMESTAMP;

                case "DT_DECIMAL":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_DECIMAL;

                case "DT_FILETIME":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_FILETIME;

                case "DT_GUID":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_GUID;

                case "DT_I1":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I1;

                case "DT_I2":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I2;

                case "DT_I4":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4;

                case "DT_I8":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I8;

                case "DT_IMAGE":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_IMAGE;

                case "DT_NTEXT":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_NTEXT;

                case "DT_NUMERIC":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_NUMERIC;

                case "DT_R4":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_R4;

                case "DT_R8":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_R8;

                case "DT_STR":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;

                case "DT_TEXT":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_TEXT;

                case "DT_UI1":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_UI1;

                case "DT_UI2":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_UI2;

                case "DT_UI4":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_UI4;

                case "DT_UI8":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_UI8;

                case "DT_WSTR":
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR;

                default:
                    return Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_NULL;
            }
        }

        private static void ValidateInputAndOutputColumns(IDTSDesigntimeComponent100 designTimeComponent, IDTSInput100 input, IDTSOutput100 output, IDTSVirtualInput100 virutalInput, IDTSVirtualInputColumnCollection100 virtualInputColumnCollection)
        {
            #region Verify and Set UsageType property for Input Columns

            foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
            {
                if (inputColumn.CustomPropertyCollection["CopyFromReferenceColumn"].Value != null)
                {
                    if (inputColumn.UsageType != DTSUsageType.UT_READWRITE)
                    {
                        inputColumn.UsageType = DTSUsageType.UT_READWRITE;
                    }
                }

                else if (inputColumn.CustomPropertyCollection["JoinToReferenceColumn"].Value != null)
                {
                    if (inputColumn.UsageType != DTSUsageType.UT_READONLY)
                    {
                        inputColumn.UsageType = DTSUsageType.UT_READONLY;
                    }
                }

                else
                {
                    designTimeComponent.SetUsageType(input.ID, virutalInput,
                        virtualInputColumnCollection[inputColumn.Name].LineageID, DTSUsageType.UT_IGNORED);
                }
            }

            #endregion

            #region Verify validity of Output Columns and remove Columns not used

            for (int outputIndex = output.OutputColumnCollection.Count - 1; outputIndex >= 0; outputIndex--)
            {
                if (String.IsNullOrEmpty(
                    output.OutputColumnCollection[outputIndex].CustomPropertyCollection["CopyFromReferenceColumn"].Value.ToString()))
                {
                    output.OutputColumnCollection.RemoveObjectByIndex(outputIndex);
                }
            }

            #endregion
        }

        private void ApplyCustomCollections(XmlNodeList referenceColumns, IDTSDesigntimeComponent100 designTimeComponent, IDTSInput100 input, IDTSOutput100 output, IDTSVirtualInput100 virutalInput, IDTSVirtualInputColumnCollection100 virtualInputColumnCollection)
        {
            foreach (XmlNode collection in collectionInformation)
            {
                string collectionName = collection.Attributes["Name"].Value;

                #region If Collection is Input Collection

                if (collection.Attributes["Type"].Value == "Input")
                {
                    if (collection.SelectSingleNode("Columns") != null)
                    {
                        XmlNodeList columns = collection.SelectSingleNode("Columns").ChildNodes;

                        if (columns != null)
                        {
                            foreach (XmlNode column in columns)
                            {
                                string columnName = column.Attributes["Name"].Value;

                                #region Check if Column is present in Virtual Input Collection

                                foreach (IDTSVirtualInputColumn100 virtualInputColumn in virtualInputColumnCollection)
                                {
                                    if (virtualInputColumn.Name == columnName)
                                    {
                                        #region Verify JoinToReferenceColumn property - Check Reference Collection to verify if column in JoinToReferenceColumn is present or not

                                        if (column.SelectSingleNode("Properties") != null)
                                        {
                                            if (column.SelectSingleNode("Properties").ChildNodes.Count > 0)
                                            {
                                                foreach (XmlNode columnProperty in column.SelectSingleNode("Properties").ChildNodes)
                                                {
                                                    if (columnProperty.Attributes["Name"].Value == "JoinToReferenceColumn")
                                                    {
                                                        string joinToReferenceColumn = columnProperty.Attributes["Value"].Value;

                                                        #region Check if JoinToReferenceColumn is present in Reference Collection and Set Property if present

                                                        foreach (XmlNode referenceColumn in referenceColumns)
                                                        {
                                                            if (referenceColumn.Attributes["name"].Value == joinToReferenceColumn
                                                                && referenceColumn.Attributes["dataType"].Value == virtualInputColumn.DataType.ToString())
                                                            {
                                                                #region Check if column is present in Input Collection

                                                                bool columnPresentInInput = false;
                                                                foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
                                                                {
                                                                    if (inputColumn.Name == virtualInputColumn.Name)
                                                                    {
                                                                        columnPresentInInput = true;
                                                                        break;
                                                                    }
                                                                }

                                                                #endregion

                                                                #region Set Property

                                                                if (!columnPresentInInput)
                                                                {
                                                                    designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                                        virtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
                                                                }

                                                                input.InputColumnCollection[virtualInputColumn.Name].UsageType = DTSUsageType.UT_READONLY;
                                                                //input.InputColumnCollection[virtualInputColumn.Name].TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;

                                                                input.InputColumnCollection[virtualInputColumn.Name].CustomPropertyCollection["JoinToReferenceColumn"].Value
                                                                    = joinToReferenceColumn;

                                                                #endregion

                                                                break;
                                                            }
                                                        }

                                                        #endregion

                                                        break;
                                                    }
                                                }
                                            }
                                        }

                                        #endregion

                                        #region Verify CopyFromReferenceColumn property - Check Reference Collection to verify if column in CopyFromReferenceColumn is present or not

                                        if (column.SelectSingleNode("Properties") != null)
                                        {
                                            if (column.SelectSingleNode("Properties").ChildNodes.Count > 0)
                                            {
                                                foreach (XmlNode columnProperty in column.SelectSingleNode("Properties").ChildNodes)
                                                {
                                                    if (columnProperty.Attributes["Name"].Value == "CopyFromReferenceColumn")
                                                    {
                                                        string copyFromReferenceColumn = columnProperty.Attributes["Value"].Value;

                                                        #region Check if CopyFromReferenceColumn is present in Reference Collection and Set Property if present

                                                        foreach (XmlNode referenceColumn in referenceColumns)
                                                        {
                                                            if (referenceColumn.Attributes["name"].Value == copyFromReferenceColumn)
                                                            {
                                                                #region Check if column is present in Input Collection

                                                                bool columnPresentInInput = false;
                                                                foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
                                                                {
                                                                    if (inputColumn.Name == virtualInputColumn.Name)
                                                                    {
                                                                        columnPresentInInput = true;
                                                                        break;
                                                                    }
                                                                }

                                                                #endregion

                                                                #region Set Property

                                                                if (!columnPresentInInput)
                                                                {
                                                                    designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                                        virtualInputColumn.LineageID, DTSUsageType.UT_READWRITE);
                                                                }

                                                                input.InputColumnCollection[virtualInputColumn.Name].UsageType = DTSUsageType.UT_READWRITE;
                                                                input.InputColumnCollection[virtualInputColumn.Name].ErrorOrTruncationOperation = "Copy Column";
                                                                input.InputColumnCollection[virtualInputColumn.Name].TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;

                                                                input.InputColumnCollection[virtualInputColumn.Name].CustomPropertyCollection["CopyFromReferenceColumn"].Value
                                                                    = copyFromReferenceColumn;

                                                                #endregion

                                                                break;
                                                            }
                                                        }

                                                        #endregion

                                                        break;
                                                    }
                                                }
                                            }
                                        }


                                        #endregion

                                        break;
                                    }
                                }

                                #endregion
                            }
                        }
                    }
                }

                #endregion

                #region If Collection is Output Collection

                else
                {
                    if (collection.SelectSingleNode("Columns") != null)
                    {
                        XmlNodeList columns = collection.SelectSingleNode("Columns").ChildNodes;

                        if (columns != null)
                        {
                            foreach (XmlNode column in columns)
                            {
                                string columnName = column.Attributes["Name"].Value;

                                #region Check if Column is present in Output Collection

                                bool columnPresentInOutput = false;

                                foreach (IDTSOutputColumn100 outputColumn in output.OutputColumnCollection)
                                {
                                    if (outputColumn.Name == columnName)
                                    {
                                        columnPresentInOutput = true;
                                        break;
                                    }
                                }

                                #endregion

                                #region Apply Properties

                                #region Verify CopyFromReferenceColumn property - Check Reference Collection to verify if column in CopyFromReferenceColumn is present or not

                                if (column.SelectSingleNode("Properties") != null)
                                {
                                    if (column.SelectSingleNode("Properties").ChildNodes.Count > 0)
                                    {
                                        foreach (XmlNode columnProperty in column.SelectSingleNode("Properties").ChildNodes)
                                        {
                                            if (columnProperty.Attributes["Name"].Value == "CopyFromReferenceColumn")
                                            {
                                                string copyFromReferenceColumn = columnProperty.Attributes["Value"].Value;

                                                #region Check if CopyFromReferenceColumn is present in Reference Collection and Set Property if present

                                                foreach (XmlNode referenceColumn in referenceColumns)
                                                {
                                                    if (referenceColumn.Attributes["name"].Value == copyFromReferenceColumn)
                                                    {
                                                        #region Add Column to Output if not present in Output Collection

                                                        if (!columnPresentInOutput)
                                                        {
                                                            IDTSOutputColumn100 outputColumn = output.OutputColumnCollection.New();
                                                            outputColumn.Name = columnName;

                                                            Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType = ConvertStringToDataType(referenceColumn.Attributes["dataType"].Value.ToString());

                                                            outputColumn.SetDataTypeProperties(dataType,
                                                                Convert.ToInt32(referenceColumn.Attributes["length"].Value), Convert.ToInt32(referenceColumn.Attributes["precision"].Value),
                                                                Convert.ToInt32(referenceColumn.Attributes["scale"].Value), Convert.ToInt32(referenceColumn.Attributes["codePage"].Value));

                                                            outputColumn.ErrorOrTruncationOperation = "Copy Column";
                                                            outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;

                                                            IDTSCustomProperty100 customProperty = outputColumn.CustomPropertyCollection.New();
                                                            customProperty.Name = "CopyFromReferenceColumn";
                                                        }

                                                        #endregion

                                                        #region Set Property

                                                        output.OutputColumnCollection[columnName].CustomPropertyCollection["CopyFromReferenceColumn"].Value
                                                            = copyFromReferenceColumn;

                                                        #endregion

                                                        break;
                                                    }
                                                }

                                                #endregion

                                                break;
                                            }
                                        }
                                    }
                                }

                                #endregion

                                #endregion
                            }
                        }
                    }
                }

                #endregion
            }
        }

        private void RestorePreviousCollections(XmlNodeList referenceColumns, IDTSDesigntimeComponent100 designTimeComponent, IDTSInput100 originalInput, IDTSInput100 input, IDTSOutput100 originalOutput, IDTSOutput100 output, IDTSVirtualInput100 virutalInput, IDTSVirtualInputColumnCollection100 virtualInputColumnCollection)
        {
            #region Add Columns to Input Collection based on Original Input Collection

            foreach (IDTSInputColumn100 originalInputColumn in originalInput.InputColumnCollection)
            {
                #region Check if Original Input Column is present in Virtual Input of New Component

                foreach (IDTSVirtualInputColumn100 virtualInputColumn in virtualInputColumnCollection)
                {
                    if (originalInputColumn.Name == virtualInputColumn.Name)
                    {
                        #region Verify JoinToReferenceColumn property - Check Reference Collection to verify if column in JoinToReferenceColumn is present or not

                        if (originalInputColumn.CustomPropertyCollection
                            ["JoinToReferenceColumn"].Value != null)
                        {
                            string joinToReferenceColumn = originalInputColumn.CustomPropertyCollection["JoinToReferenceColumn"].Value.ToString();

                            #region Check if JoinToReferenceColumn is present in Reference Collection and Set Property if present

                            foreach (XmlNode referenceColumn in referenceColumns)
                            {
                                if (referenceColumn.Attributes["name"].Value == joinToReferenceColumn
                                    && referenceColumn.Attributes["dataType"].Value == virtualInputColumn.DataType.ToString())
                                {
                                    #region Check if column is present in Input Collection

                                    bool columnPresentInInput = false;
                                    foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
                                    {
                                        if (inputColumn.Name == virtualInputColumn.Name)
                                        {
                                            columnPresentInInput = true;
                                            break;
                                        }
                                    }

                                    #endregion

                                    #region Set Property

                                    if (!columnPresentInInput)
                                    {
                                        designTimeComponent.SetUsageType(input.ID, virutalInput,
                                            virtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
                                    }

                                    input.InputColumnCollection[virtualInputColumn.Name].UsageType = DTSUsageType.UT_READONLY;
                                    //input.InputColumnCollection[virtualInputColumn.Name].TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;

                                    input.InputColumnCollection[virtualInputColumn.Name].CustomPropertyCollection["JoinToReferenceColumn"].Value
                                        = joinToReferenceColumn;

                                    #endregion

                                    break;
                                }
                            }

                            #endregion
                        }
                        #endregion

                        #region Verify CopyFromReferenceColumn property - Check Reference Collection to verify if column in CopyFromReferenceColumn is present or not

                        if (originalInputColumn.CustomPropertyCollection
                            ["CopyFromReferenceColumn"].Value != null)
                        {
                            string copyFromReferenceColumn = originalInputColumn.CustomPropertyCollection["CopyFromReferenceColumn"].Value.ToString();

                            #region Check if CopyFromReferenceColumn is present in Reference Collection and Set Property if present

                            foreach (XmlNode referenceColumn in referenceColumns)
                            {
                                if (referenceColumn.Attributes["name"].Value == copyFromReferenceColumn)
                                {
                                    #region Check if column is present in Input Collection

                                    bool columnPresentInInput = false;
                                    foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
                                    {
                                        if (inputColumn.Name == virtualInputColumn.Name)
                                        {
                                            columnPresentInInput = true;
                                            break;
                                        }
                                    }

                                    #endregion

                                    #region Set Property

                                    if (!columnPresentInInput)
                                    {
                                        designTimeComponent.SetUsageType(input.ID, virutalInput,
                                            virtualInputColumn.LineageID, DTSUsageType.UT_READWRITE);
                                    }

                                    input.InputColumnCollection[virtualInputColumn.Name].UsageType = DTSUsageType.UT_READWRITE;
                                    input.InputColumnCollection[virtualInputColumn.Name].ErrorOrTruncationOperation = "Copy Column";
                                    input.InputColumnCollection[virtualInputColumn.Name].TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;

                                    input.InputColumnCollection[virtualInputColumn.Name].CustomPropertyCollection["CopyFromReferenceColumn"].Value
                                        = copyFromReferenceColumn;

                                    #endregion

                                    break;
                                }
                            }

                            #endregion
                        }
                        #endregion

                        break;
                    }
                }

                #endregion
            }

            #endregion

            #region Add Columns to Output Collection based on Original Output Collection

            foreach (IDTSOutputColumn100 originalOutputColumn in originalOutput.OutputColumnCollection)
            {
                #region Verify CopyFromReferenceColumn property - Check Reference Collection to verify if column in CopyFromReferenceColumn is present or not

                if (originalOutputColumn.CustomPropertyCollection["CopyFromReferenceColumn"].Value == null)
                    break;

                string copyFromReferenceColumn = originalOutputColumn.CustomPropertyCollection["CopyFromReferenceColumn"].Value.ToString();

                #region Check if CopyFromReferenceColumn is present in Reference Collection and Set Property if present

                foreach (XmlNode referenceColumn in referenceColumns)
                {
                    if (referenceColumn.Attributes["name"].Value == copyFromReferenceColumn)
                    {
                        #region Check if column is present in Output Collection

                        bool columnPresentInOutput = false;
                        foreach (IDTSOutputColumn100 outputColumn in output.OutputColumnCollection)
                        {
                            if (outputColumn.Name == originalOutputColumn.Name)
                            {
                                columnPresentInOutput = true;
                                break;
                            }
                        }

                        #endregion

                        #region Add Column to Output if not present in Output Collection

                        if (!columnPresentInOutput)
                        {
                            IDTSOutputColumn100 outputColumn = output.OutputColumnCollection.New();
                            outputColumn.Name = originalOutputColumn.Name;

                            Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType = ConvertStringToDataType(referenceColumn.Attributes["dataType"].Value.ToString());

                            outputColumn.SetDataTypeProperties(dataType,
                                Convert.ToInt32(referenceColumn.Attributes["length"].Value), Convert.ToInt32(referenceColumn.Attributes["precision"].Value),
                                Convert.ToInt32(referenceColumn.Attributes["scale"].Value), Convert.ToInt32(referenceColumn.Attributes["codePage"].Value));

                            outputColumn.ErrorOrTruncationOperation = "Copy Column";
                            outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;

                            IDTSCustomProperty100 customProperty = outputColumn.CustomPropertyCollection.New();
                            customProperty.Name = "CopyFromReferenceColumn";
                        }

                        #endregion

                        #region Set Property

                        output.OutputColumnCollection[originalOutputColumn.Name].CustomPropertyCollection["CopyFromReferenceColumn"].Value
                            = copyFromReferenceColumn;

                        #endregion

                        break;
                    }
                }

                #endregion

                #endregion

                break;
            }

            #endregion
        }

        private bool SetReferenceMetadataXml(out XmlNodeList referenceColumns, IDTSDesigntimeComponent100 designTimeComponent)
        {
            XmlDocument referenceMetadataXml = null;
            referenceColumns = null;

            designTimeComponent.AcquireConnections(null);

            // To get ReferenceMetadataXml, Lookup needs to be initialized
            try
            {
                designTimeComponent.ReinitializeMetaData();
                designTimeComponent.ReleaseConnections();
            }
            catch
            {
                designTimeComponent.ReleaseConnections();
                return false;
            }

            referenceMetadataXml = new XmlDocument();
            referenceMetadataXml.LoadXml(component.CustomPropertyCollection["ReferenceMetadataXml"].Value.ToString());
            referenceColumns = referenceMetadataXml.GetElementsByTagName("referenceColumn");

            return true;
        }


        #endregion
    }
}
